﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace Empleados
{
    public class conexionBD
    {
        #region PROPIEDADES
        private string _Servidor;
        private string _BD;
        private string _Usuario;
        private string _Password;
        private string _TipoSeguridad;

        private string Servidor
        {
            get
            {
                return _Servidor;
            }
            set
            {
                _Servidor = value;
            }
        }
        private string BD
        {
            get
            {
                return _BD;
            }
            set
            {
                _BD = value;
            }
        }
        private string Usuario
        {
            get
            {
                return _Usuario;
            }
            set
            {
                _Usuario = value;
            }
        }
        private string Password
        {
            get
            {
                return _Password;
            }
            set
            {
                _Password = value;
            }
        }
        private string TipoSeguridad
        {
            get
            {
                return _TipoSeguridad;
            }
            set
            {
                _TipoSeguridad = value;
            }
        }
        #endregion

        public static conexionBD instancia;
        private SqlConnection _SqlConn;
        public SqlConnection SqlConn
        {
            get { return _SqlConn; }
            private set { _SqlConn = value; }
        }

        private conexionBD()
        {
            try
            {
                string strConexionSQL;

                Servidor = "Empleados.mssql.somee.com";
                BD = "Empleados";
                Usuario = "jessicavaltierra_SQLLogin_1";
                Password = "is85v5aaar";

                strConexionSQL = "Data Source=" + Servidor + ";Initial Catalog=" + BD + ";";
                if (_TipoSeguridad == "NT")
                    strConexionSQL += "Integrated Security=SSPI;";
                else
                {
                    strConexionSQL += "User Id=" + Usuario + ";";
                    strConexionSQL += "Password=" + Password + ";";
                }


                this.SqlConn = new SqlConnection(strConexionSQL);

            }
            catch (Exception ex)
            {
                MessageBox.Show("No existe conexión con la base de datos." + "\n" + ex.Message,
                    "Mi Aplicación.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public static conexionBD GetInstance()
        {
            if (instancia == null)
                instancia = new conexionBD();
            return instancia;
        }

        public void CerrarConexion()
        {
            this.SqlConn.Dispose();
            instancia = null;

        }

        public bool FExecuteQuery(ref string strSQL)
        {
            bool bndResult = false;
            SqlCommand cmd = new SqlCommand(strSQL, this.SqlConn);
            try
            {
                if (this.SqlConn.State == ConnectionState.Closed)
                    this.SqlConn.Open();
                cmd.CommandTimeout = 600;
                cmd.ExecuteNonQuery();
                bndResult = true;
            }
            catch (Exception e)
            {
                strSQL = "Error al ejecutar query: " + strSQL + ".";
                strSQL += "\n ERROR: " + e.Message;
                //MessageBox.Show(strError, "Mi Aplicación", MessageBoxButtons.OK, MessageBoxIcon.Error);
                bndResult = false;
            }
            finally
            {
                this.SqlConn.Close();
            }
            return bndResult;
        }


        public DataTable FGetDataTable(string strSQL)
        {
            DataTable result = new DataTable();

            try
            {
                SqlDataAdapter SqlDA = new SqlDataAdapter(strSQL, this.SqlConn);
                SqlDA.Fill(result);
            }
            catch (SqlException ex)
            {
                result = null;
            }
            return result;
        }


        public object FGetValue(string strSQL)
        {
            SqlCommand SqlComando = new SqlCommand();
            object objValor = null;
            try
            {
                if (this.SqlConn.State == ConnectionState.Closed)
                    this.SqlConn.Open();
                SqlComando.Connection = this.SqlConn;
                SqlComando.CommandType = CommandType.Text;
                SqlComando.CommandText = strSQL;
                objValor = SqlComando.ExecuteScalar();
            }
            catch (Exception)
            {
                objValor = null;
            }
            finally
            {
                this.SqlConn.Close();
            }
            return objValor;
        }

        public DataSet ObtenerDatosSet(string strSQL)
        {
            DataSet datos = new DataSet();

            try
            {
                SqlDataAdapter SqlDA = new SqlDataAdapter(strSQL, this._SqlConn);
                SqlDA.Fill(datos);
            }
            catch (SqlException ex)
            {
                datos = null;
            }
            return datos;
        }

    }
}
